﻿package week14_database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

/**
 * 该实现类运行需要MySql数据库test和表student
 * create table studentTest(
	id int not null primary key auto_increment,
	name varchar(20) not null
	);
	
 * @author zhrb
 *
 */

public class StudentDaoJDBCImpl implements StudentDao {

	private static String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private static String driverName = "com.mysql.jdbc.Driver";
	private static String userName = "root";
	private static String password = "ty123456";
	

	
	@Override
	public boolean writeStudent(Student student){
		String sql = "insert into student(name) values(?) ";//表中有id和name这列
		int result = -1;
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setString(1, student.getName());
			result = pstat.executeUpdate();
		}catch (SQLException | ClassNotFoundException sqle) {
			sqle.printStackTrace();
		}
		return result>0?true:false;
	}
	
	@Override
	public Student readStudent(String name) {
		ResultSet rs = null;
		String sql = "select * from student where name like ?";//表中有id和name这列
		Student student = null;
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while(rs.next()){
				int stuId = rs.getInt("id");
				String stuName = rs.getString("name");
				student = new Student(stuId, stuName);
			}
		}catch (SQLException | ClassNotFoundException sqle) {
			sqle.printStackTrace();
		}
		return student;
	}
	
	@Override
	public void diplayAllStudent() {
		String sql = "select * from student";//表中有id和name这列
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			Statement stat=conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				System.out.format("该学生的id=%d,姓名=%s\n",id,name);
			}
		}catch (SQLException | ClassNotFoundException sqle) {
			sqle.printStackTrace();
		}
	}

	@Override
	public List<Student> getAllStudents() {
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student";//表中有id和name这列
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			Statement stat=conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()){
				list.add(new Student(rs.getInt("id"),rs.getString("name")));
			}
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public static String getRandomString(int length) { //length表示生成字符串的长度
	    String base = "abcdefghijklmnopqrstuvwxyz0123456789";   
	    Random random = new Random();   
	    StringBuffer sb = new StringBuffer();   
	    for (int i = 0; i < length; i++) {   
	        int number = random.nextInt(base.length());   
	        sb.append(base.charAt(number));   
	    }   
	    return sb.toString();   
	 }  

	@Override
	public int add(Student stu) {
		int rs=0;
		String sql = "insert into student(id,name) values(?,?); ";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setInt(1, stu.getId());
			pstat.setString(2, stu.getName());
			rs = pstat.executeUpdate();
		}catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	@Override
	public int delete(int sid) {
		int rs=0;
		String sql = "delete from student where id=?";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setInt(1, sid);
			rs = pstat.executeUpdate();
		}catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	@Override
	public int update(Student stu) {
		int rs=0;
		String sql = "update student set name=? where id=?";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setString(1, stu.getName());
			pstat.setInt(2, stu.getId());
			rs = pstat.executeUpdate();
		}catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	@Override
	public List<Student> findAll() {
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			Statement stat=conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while(rs.next()){
				list.add(new Student(rs.getInt("id"),rs.getString("name")));
			}
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Student findById(String sid) {
		Student stu=null;
		String sql = "select * from student where id=?";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setInt(1, Integer.parseInt(sid));
			ResultSet rs = pstat.executeQuery();
			while(rs.next())
			stu=new Student(rs.getInt("id"),rs.getString("name"));
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stu;
	}

	@Override
	public List<Student> findByName(String name) {
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student where name like ?";
		try(Connection conn = DriverManager.getConnection(URL, userName, password)) {
			Class.forName(driverName);
			PreparedStatement pstat = conn.prepareStatement(sql);
			pstat.setString(1, "%"+name+"%");
			ResultSet rs = pstat.executeQuery();
			while(rs.next()){
				list.add(new Student(rs.getInt("id"),rs.getString("name")));
			}
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public Map<String,Student> maptest(){
		Map<String,Student> smap=new HashMap<String,Student>();
		List<Student> list =findAll();
		for (Student e : list) {
			smap.put(e.getName(), e);
		}
		return smap;
	}
	
	public Student findByName1(String name){
		String sql="select *from student where name="+name;
		Student stu=null;
		try(Connection conn=DriverManager.getConnection(URL, userName, password)){
			Class.forName(driverName);
			Statement st=conn.createStatement();
			ResultSet rs=st.executeQuery(sql);
			rs.next();
			stu=new Student(rs.getInt("id"),rs.getString("name"));
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stu;
	}
	

}
